package com.zb.frame.modules.sys.upgrade;

import com.zb.frame.base.upgrade.UpgradeColumn;
import com.zb.frame.base.upgrade.UpgradeTable;
import com.zb.frame.base.upgrade.common.UpgradeTask;
import com.zb.frame.base.upgrade.enums.DBType;
import com.zb.frame.base.upgrade.enums.ValueType;
import com.zb.frame.modules.sys.utils.UpgradeUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.SQLException;

public class UpgradeTask_New extends UpgradeTask {
	
	private static final Logger logger = LoggerFactory.getLogger(UpgradeTask_New.class);

	/**
	 * 新增表sys_user、sys_role、sys_rel_user_role、sys_menu、sys_rel_role_menu
	 * @param conn	JDBC链接
	 * @param type	数据库类型
	 * @return
	 */
	@Override
	public boolean doUpgrade(Connection conn, DBType type) {
		try {
			createTableUser(conn, type, "sys_user", "系统用户表");
			createTableSysRole(conn, type, "sys_role", "系统角色表");
			createTableRelUserRole(conn, type, "sys_rel_user_role", "用户角色关系表");
			createTableSysMenu(conn, type, "sys_menu", "菜单资源表");
			createTableRelMenuRole(conn, type, "sys_rel_role_menu", "角色资源关系表");
			return true;
		} catch(Exception e) {
			logger.error("Upgrade module '" + this.getClass().getPackage().getName() + "' to " + getNewVersion() + " fail.", e);
			return false;
		}
	}


	/**
	 *创建表-用户表
	 * @param conn		jdbc连接
	 * @param type		数据库类型
	 * @param tableName	sys_user
	 * @throws SQLException
	 */
	private void createTableUser(Connection conn, DBType type, String tableName, String comment) throws SQLException {
		if(UpgradeUtils.checkTableExist(conn,tableName, this.getClass().getPackage().getName()))
			return;
		UpgradeTable table = new UpgradeTable(tableName, comment);
		table.addColumns(new UpgradeColumn[] {
				new UpgradeColumn("user_id", "用户主键", ValueType.LONG, false, null),
				new UpgradeColumn("user_name", "登录账号", ValueType.STRING_VARCHAR, true, 20),
				new UpgradeColumn("user_password", "登录密码", ValueType.STRING_VARCHAR, true,32),
				new UpgradeColumn("user_alias", "用户别名", ValueType.STRING_VARCHAR, true, 20),
				new UpgradeColumn("user_phone", "手机号码", ValueType.STRING_VARCHAR, true, 20),
				new UpgradeColumn("user_email", "邮箱", ValueType.STRING_VARCHAR, true, 40),
				new UpgradeColumn("user_sex", "用户性别", ValueType.INT, true, null),
				new UpgradeColumn("user_desc", "用户描述", ValueType.STRING_VARCHAR, true, 200),
				new UpgradeColumn("user_avatar", "头像图片路径", ValueType.STRING_VARCHAR, true, 50),
				new UpgradeColumn("user_status", "用户状态0-停用1-正常2-上锁", ValueType.INT, true, null),
				new UpgradeColumn("create_user", "创建人", ValueType.LONG, true, null),
				new UpgradeColumn("create_time", "创建时间", ValueType.DATETIME, true, null),
				new UpgradeColumn("update_user", "修改人", ValueType.LONG, true, null),
				new UpgradeColumn("update_time", "修改时间", ValueType.DATETIME, true, null)
		});
		table.addPrimaryKeys("user_id");
		table.addIdentityKeys("user_id");
		table.doCreateTable(conn, type);
	}

	/**
	 * 创建表-角色表
	 * @param conn		jdbc连接
	 * @param type		数据库类型
	 * @param tableName	sys_role
	 * @throws SQLException
	 */
	private void createTableSysRole(Connection conn, DBType type, String tableName, String comment) throws SQLException {
		if(UpgradeUtils.checkTableExist(conn,tableName, this.getClass().getPackage().getName()))
			return;
		UpgradeTable table = new UpgradeTable(tableName, comment);
		table.addColumns(new UpgradeColumn[] {
				new UpgradeColumn("role_id", "角色主键", ValueType.STRING_VARCHAR, false, 20),
				new UpgradeColumn("role_name", "角色名称", ValueType.STRING_VARCHAR, true, 20),
				new UpgradeColumn("role_desc", "描述", ValueType.STRING_VARCHAR, true,200),
				new UpgradeColumn("is_sys", "是否内置角色", ValueType.INT, true, null),
				new UpgradeColumn("create_user", "创建人", ValueType.LONG, true, null),
				new UpgradeColumn("create_time", "创建时间", ValueType.DATETIME, true, null),
				new UpgradeColumn("update_user", "修改人", ValueType.LONG, true, null),
				new UpgradeColumn("update_time", "修改时间", ValueType.DATETIME, true, null)
		});
		table.addPrimaryKeys("role_id");
		table.doCreateTable(conn, type);
	}

	/**
	 * 创建表-角色用户关系表
	 * @param conn		jdbc连接
	 * @param type		数据库类型
	 * @param tableName	sys_rel_user_role
	 * @throws SQLException
	 */
	private void createTableRelUserRole(Connection conn, DBType type, String tableName, String comment) throws SQLException {
		if(UpgradeUtils.checkTableExist(conn,tableName, this.getClass().getPackage().getName()))
			return;
		UpgradeTable table = new UpgradeTable(tableName, comment);
		table.addColumns(new UpgradeColumn[] {
				new UpgradeColumn("rel_id", "主键", ValueType.LONG, false, null),
				new UpgradeColumn("user_id", "用户ID", ValueType.LONG, true, null),
				new UpgradeColumn("role_id", "角色ID", ValueType.STRING_VARCHAR, true,20),
				new UpgradeColumn("create_user", "创建人", ValueType.LONG, true, null),
				new UpgradeColumn("create_time", "创建时间", ValueType.DATETIME, true, null),
				new UpgradeColumn("update_user", "修改人", ValueType.LONG, true, null),
				new UpgradeColumn("update_time", "修改时间", ValueType.DATETIME, true, null)
		});
		table.addPrimaryKeys("rel_id");
		table.addIdentityKeys("rel_id");
		table.doCreateTable(conn, type);
	}


	/**
	 *创建表-菜单资源表
	 * @param conn		jdbc连接
	 * @param type		数据库类型
	 * @param tableName	sys_menu
	 * @throws SQLException
	 */
	private void createTableSysMenu(Connection conn, DBType type, String tableName, String comment) throws SQLException {
		if(UpgradeUtils.checkTableExist(conn,tableName, this.getClass().getPackage().getName()))
			return;
		UpgradeTable table = new UpgradeTable(tableName, comment);
		table.addColumns(new UpgradeColumn[] {
				new UpgradeColumn("menu_id", "菜单主键", ValueType.LONG, false, null),
				new UpgradeColumn("p_id", "上级菜单ID", ValueType.LONG, true, null),
				new UpgradeColumn("menu_name", "菜单名称", ValueType.STRING_VARCHAR, true,20),
				new UpgradeColumn("menu_alias", "菜单别名", ValueType.STRING_VARCHAR, true, 20),
				new UpgradeColumn("menu_icon", "icon图标", ValueType.STRING_VARCHAR, true, 20),
				new UpgradeColumn("menu_type", "类型1-目录2-菜单3-操作", ValueType.INT, true, null),
				new UpgradeColumn("menu_path", "路由路径", ValueType.STRING_VARCHAR, true, 40),
				new UpgradeColumn("menu_perm", "操作权限", ValueType.STRING_VARCHAR, true,40),
				new UpgradeColumn("menu_desc", "描述", ValueType.STRING_VARCHAR, true,200),
				new UpgradeColumn("menu_order", "排序", ValueType.INT, true,null),
				new UpgradeColumn("create_user", "创建人", ValueType.LONG, true, null),
				new UpgradeColumn("create_time", "创建时间", ValueType.DATETIME, true, null),
				new UpgradeColumn("update_user", "修改人", ValueType.LONG, true, null),
				new UpgradeColumn("update_time", "修改时间", ValueType.DATETIME, true, null)
		});
		table.addPrimaryKeys("menu_id");
		table.addIdentityKeys("menu_id");
		table.doCreateTable(conn, type);
	}

	/**
	 *创建表-菜单角色关联表
	 * @param conn		jdbc连接
	 * @param type		数据库类型
	 * @param tableName	sys_menu_role_rel
	 * @throws SQLException
	 */
	private void createTableRelMenuRole(Connection conn, DBType type, String tableName, String comment) throws SQLException {
		if(UpgradeUtils.checkTableExist(conn,tableName, this.getClass().getPackage().getName()))
			return;
		UpgradeTable table = new UpgradeTable(tableName, comment);
		table.addColumns(new UpgradeColumn[] {
				new UpgradeColumn("rel_id", "主键", ValueType.LONG, false, null),
				new UpgradeColumn("menu_id", "菜单资源ID", ValueType.LONG, true, null),
				new UpgradeColumn("role_id", "角色ID", ValueType.STRING_VARCHAR, true,20),
				new UpgradeColumn("create_user", "创建人", ValueType.LONG, true, null),
				new UpgradeColumn("create_time", "创建时间", ValueType.DATETIME, true, null),
				new UpgradeColumn("update_user", "修改人", ValueType.LONG, true, null),
				new UpgradeColumn("update_time", "修改时间", ValueType.DATETIME, true, null)
		});
		table.addPrimaryKeys("rel_id");
		table.addIdentityKeys("rel_id");
		table.doCreateTable(conn, type);
	}

	@Override
	public String getNewVersion() {
		return "0.0.1";
	}

}
